use shq_common::prelude::get_my_pool;
use sqlx::Row;

use crate::get_flow_run_props;

pub(crate) async fn get_comp_divi_emids(
    run_id: u64,
    _step_id: u8,
) -> anyhow::Result<Vec<u32>> {
    let sql = "select acid from comp_acco_divi_flow_new WHERE frid=?";
    let acid: Option<u64>= sqlx::query_scalar(&sql)
        .bind(&run_id)
        .fetch_optional(get_my_pool())
        .await?;
    if acid.is_none() {
        return anyhow::Ok(vec![]);
    }
    let acid = acid.unwrap() as u64;
    let sql = "SELECT emid FROM comp_acco_divi WHERE acid=? AND iswo=0 AND wodt IS NULL";
    let emids: Vec<u32> = sqlx::query_scalar(&sql)
        .bind(&acid)
        .fetch_all(get_my_pool())
        .await?;
    Ok(emids)
}

pub(crate) async fn get_indi_divi_emids(
    run_id: u64,
    _step_id: u8,
) -> anyhow::Result<Vec<u32>> {
    let sql = "select acid from indi_acco_divi_flow_update WHERE frid=?";
    let row = sqlx::query(&sql)
        .bind(&run_id)
        .fetch_one(get_my_pool())
        .await?;
    let acid: Option<u64> = row.try_get(0)?;
    if acid.is_none() {
        return anyhow::Ok(vec![]);
    }
    let acid = acid.unwrap() as u64;
    let sql = "SELECT emid FROM indi_acco_divi WHERE acid=? AND iswo=0 AND wodt IS NULL";
    let rows = sqlx::query(&sql)
        .bind(&acid)
        .fetch_all(get_my_pool())
        .await?;
    let mut emids: Vec<u32> = vec![];
    for row in rows.iter() {
        let emid: u32 = row.try_get(0)?;
        emids.push(emid);
    }
    Ok(emids)
}
pub(crate) async fn get_hr_management_specialist(
    run_id: u64,
    _step_id: u8,
) -> anyhow::Result<Vec<u32>> {
    let flow_props=get_flow_run_props(run_id).await?;
    let table_name=match flow_props.wfid {
        1=>"hr_credit_report_run",
        2=>"hr_transaction_run",
        3=>"hr_home_visit_run",
        _=> anyhow::bail!("不支持的流程ID:{}", flow_props.wfid),
    };
    let sql = format!("select ny,dpid from {table_name} WHERE frid=?");
    let row = sqlx::query(&sql)
        .bind(&run_id)
        .fetch_one(get_my_pool())
        .await?;
    let ny: u32 = row.try_get(0)?;
    let dpid: u32 = row.try_get(1)?;
    let sql = "SELECT emid FROM hr_dept_approvers WHERE ny=? AND dpid=? AND wfid=?";
    let rows = sqlx::query(&sql)
        .bind(&ny)
        .bind(&dpid)
        .bind(&flow_props.wfid)
        .fetch_all(get_my_pool())
        .await?;
    let mut emids: Vec<u32> = vec![];
    for row in rows.iter() {
        let emid: u32 = row.try_get(0)?;
        emids.push(emid);
    }
    Ok(emids)
}

pub(crate) async fn get_hr_supple_person(
    run_id: u64,
    _step_id: u8,
) -> anyhow::Result<Vec<u32>> {
    let flow_props=get_flow_run_props(run_id).await?;
    let table_name=match flow_props.wfid {
        1=>"hr_credit_report_run",
        2=>"hr_transaction_run",
        3=>"hr_home_visit_run",
        _=> anyhow::bail!("不支持的流程ID:{}", flow_props.wfid),
    };
    let sql = format!("select emid from {table_name} WHERE frid=?");
    let rows = sqlx::query(&sql)
        .bind(&run_id)
        .fetch_all(get_my_pool())
        .await?;
    let mut emids: Vec<u32> = vec![];
    for row in rows.iter() {
        let emid: u32 = row.try_get(0)?;
        emids.push(emid);
    }
    Ok(emids)
}